package com.linkstec.loader;

import java.io.InputStream;
import java.net.URL;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.linkstec.util.BatchContext;

public class Dao {
	private static final Logger logger = LogManager.getLogger();
	private static Pattern p = Pattern.compile("#\\{([^\\}]+)\\}");
	private static Pattern p2 = Pattern.compile("\\$\\{([^\\}]+)\\}");
	private static Dao ins;
	private static boolean initialized = false;

	private ThreadLocal<Connection> tlConn = new ThreadLocal<Connection>();
	private ThreadLocal<Map<String, PreparedStatement>> tlStatement = new ThreadLocal<Map<String, PreparedStatement>>();

	private static final class DaoSql {
		String sql;
		List<String> fields;
		List<String> placeholders;
	}

	private static Map<String, DaoSql> cacheSql = new HashMap<String, DaoSql>();

	private Dao() {

	}

	public static Dao getInstance() {
		init();
		if (ins == null) {
			synchronized (Dao.class) {
				if (ins == null) {
					ins = new Dao();
				}
			}

		}
		return ins;
	}

	private static void init() {
		if (initialized) {
			return;
		}
		initialized = true;
		Enumeration<URL> urls = null;
		try {
			urls = Thread.currentThread().getContextClassLoader()
					.getResources("sql.properties");
			while (urls.hasMoreElements()) {
				URL url = urls.nextElement();
				Properties prop = new Properties();

				InputStream input = url.openStream();

				prop.load(input);
				input.close();
				for (Iterator<String> it = prop.stringPropertyNames()
						.iterator(); it.hasNext();) {
					String id = it.next();
					String sql = prop.getProperty(id);
					List<String> lst = new LinkedList<String>();
					Matcher m = p.matcher(sql);

					StringBuffer sb = new StringBuffer();

					while (m.find()) {
						m.appendReplacement(sb, "?");
						lst.add(m.group(1));

					}

					m.appendTail(sb);

					sql = sb.toString();
					List<String> lstp = new LinkedList<String>();
					m = p2.matcher(sql);
					while (m.find()) {
						lstp.add(m.group(1));
					}

					DaoSql ds = new DaoSql();
					ds.sql = sql;
					ds.fields = lst;
					ds.placeholders = lstp;
					cacheSql.put(id, ds);
				}
			}
			logger.info("加载sql文件完成:" + cacheSql);
		} catch (Exception e) {
			logger.error("", e);
		}

	}

	public void openConnInThread() {
		init();
		try {
			Connection conn = tlConn.get();
			if (conn == null) {
				conn = BatchContext.getNewConnection();
				tlConn.set(conn);
			}

		} catch (Exception e) {
			logger.error("", e);
		}

	}

	public void closeConnInThread() {
		Map<String, PreparedStatement> m = tlStatement.get();
		if (m != null) {
			for (Iterator<PreparedStatement> it = m.values().iterator(); it
					.hasNext();) {
				PreparedStatement ps = it.next();
				if (ps != null) {
					try {
						ps.close();
					} catch (SQLException e) {
						logger.error("", e);
					}
				}
			}
		}
		tlStatement.remove();
		Connection conn = tlConn.get();
		BatchContext.closeConnection(conn);
		tlConn.remove();
	}

	public ResultSet query(String sql) {
		
		Map<String, PreparedStatement> m = tlStatement.get();
		if (m == null) {
			m = new HashMap<String, PreparedStatement>();
			tlStatement.set(m);
		}
		PreparedStatement ps = m.get(sql);
		
		if (ps == null) {
			Connection conn = tlConn.get();
			if (conn == null) {
				throw new RuntimeException("无DB连接，需先调用openConnInThread");
			}
			try {
				ps = conn.prepareStatement(sql);
				m.put(sql, ps);
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		if (ps == null) {
			throw new RuntimeException("执行SQL错误，sql=" + sql);
		}
		ResultSet rs = null;
		try {
			rs = ps.executeQuery();
		} catch (SQLException e) {
			logger.error("", e);
			e.printStackTrace();
		}
		return rs;
	}
	public ResultSet query(String sqlId, Map<String, Object> param) {
		DaoSql ds = cacheSql.get(sqlId);
		if (ds == null) {
			throw new RuntimeException("SQL不存在,id=" + sqlId);
		}
		String sql = replaceSqlPlaceholder(ds, param);

		Map<String, PreparedStatement> m = tlStatement.get();
		if (m == null) {
			m = new HashMap<String, PreparedStatement>();
			tlStatement.set(m);
		}
		PreparedStatement ps = m.get(sql);

		if (ps == null) {
			Connection conn = tlConn.get();
			if (conn == null) {
				throw new RuntimeException("无DB连接，需先调用openConnInThread");
			}
			try {
				ps = conn.prepareStatement(sql);
				m.put(sql, ps);
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		if (ps == null) {
			throw new RuntimeException("执行SQL错误，id=" + sqlId);
		}
		ResultSet rs = null;
		try {
			// 设置参数

			int i = 0;
			for (Iterator<String> it = ds.fields.iterator(); it.hasNext();) {
				String field = it.next();
				if (param != null) {
					ps.setObject(i + 1, param.get(field));
				} else {
					ps.setObject(i + 1, null);
				}
				i++;
			}

			rs = ps.executeQuery();
		} catch (SQLException e) {
			logger.error("", e);
			e.printStackTrace();
		}
		return rs;
	}

	public int insertUpdateSQL(String sqlId, Map<String, Object> param) {
		DaoSql ds = cacheSql.get(sqlId);
		if (ds == null) {
			throw new RuntimeException("SQL不存在,id=" + sqlId);
		}
		String sql = replaceSqlPlaceholder(ds, param);

		 int reInt=0;
		PreparedStatement ps =null;

		if (ps == null) {
			Connection conn = tlConn.get();
			if (conn == null) {
				throw new RuntimeException("无DB连接，需先调用openConnInThread");
			}
			try {
				logger.info("insertupdate:"+sql);
				ps = conn.prepareStatement(sql);
				
				
				int i = 0;
				for (Iterator<String> it = ds.fields.iterator(); it.hasNext();) {
					String field = it.next();
					if (param != null) {
						ps.setObject(i + 1, param.get(field));
					} else {
						ps.setObject(i + 1, null);
					}
					i++;
				}
				
				reInt=ps.executeUpdate();
				conn.commit();
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		if (ps == null) {
			throw new RuntimeException("执行SQL错误，id=" + sqlId);
		}
		return reInt;
	}

	private static Statement psp=null;
	public boolean insertTest(String sql ) {
	 
		 
		Statement ps = null;
          if(ps==null)
        	  ps=psp;  
		if (ps == null) {
			//this.openConnInThread(driver, url, username, password);
			Connection conn = tlConn.get();
			if (conn == null) {
				throw new RuntimeException("无DB连接，需先调用openConnInThread");
			}
			try {
				ps = conn.createStatement();
				psp=ps;
			} catch (SQLException e) {
				logger.error("", e);
			}
		}
		if (ps == null) {
			throw new RuntimeException("执行SQL错误，id=" + sql);
		}
		boolean rs = true;
		try {
			 
			rs = ps.execute(sql);
		} catch (SQLException e) {
			logger.error("", e);
		}finally{
			
		}
		return rs;
	}
	
	private List<Map<String, Object>> convertResultSetToList(ResultSet rs)
			throws SQLException {
		List<Map<String, Object>> lst = null;
		if (rs != null) {
			lst = new LinkedList<Map<String, Object>>();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String, Object> m = new HashMap<String, Object>();

				for (int i = 0; i < columnCount; i++) {
					String columnName = rsmd.getColumnName(i + 1);
					m.put(columnName, rs.getObject(i + 1));
				}
				lst.add(m);
			}
		}

		return lst;
	}

	@SuppressWarnings("rawtypes")
	private String replaceSqlPlaceholder(DaoSql ds, Map<String, Object> param) {
		String sql = ds.sql;
		// 替换placeholder
		for (String s : ds.placeholders) {
			Object v = param.get(s);
			if (v == null) {
				throw new RuntimeException("占位符 " + s + " 缺失,sql=" + sql);
			}
			String ph = null;
			if (v instanceof List) {
				List lstP = (List) v;
				List<String> tmp = new ArrayList<String>(lstP.size());
				for (Object p : lstP) {
					if (p instanceof String) {
						tmp.add("'" + p + "'");
					} else {
						tmp.add(p.toString());
					}
				}
				ph = "(" + StringUtils.join(tmp, ",") + ")";
			} else {
				// 字符串替换
				ph = v.toString();
			}
			if ("".equals(ph)) {
				sql = sql.replace("${" + s + "}", "null");
			} else {
				sql = sql.replace("${" + s + "}", ph);
			}
		}
		return sql;
	}

	/**
	 * 供给外部调用
	 * @param sqlId
	 * @param param
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String replaceSqlPlaceholder(String sqlId, Map<String, Object> param) {
		DaoSql ds = cacheSql.get(sqlId);
		if (ds == null) {
			throw new RuntimeException("SQL不存在,id=" + sqlId);
		}
		String sql = ds.sql;
		// 替换placeholder
		for (String s : ds.placeholders) {
			Object v = param.get(s);
			if (v == null) {
				throw new RuntimeException("占位符 " + s + " 缺失,sql=" + sql);
			}
			String ph = null;
			if (v instanceof List) {
				List lstP = (List) v;
				List<String> tmp = new ArrayList<String>(lstP.size());
				for (Object p : lstP) {
					if (p instanceof String) {
						tmp.add("'" + p + "'");
					} else {
						tmp.add(p.toString());
					}
				}
				ph = "(" + StringUtils.join(tmp, ",") + ")";
			} else {
				// 字符串替换
				ph = v.toString();
			}
			if ("".equals(ph)) {
				sql = sql.replace("${" + s + "}", "null");
			} else {
				sql = sql.replace("${" + s + "}", ph);
			}
		}
		return sql;
	}

	public List<Map<String, Object>> findListWithHolder(String sqlId,
			Map<String, Object> param) {
		List<Map<String, Object>> rv = null;

		DaoSql ds = cacheSql.get(sqlId);
		if (ds == null) {
			throw new RuntimeException("SQL不存在,id=" + sqlId);
		}
		String sql = ds.sql;
		// 替换placeholder
		sql = replaceSqlPlaceholder(ds, param);

		Connection conn = tlConn.get();
		if (conn == null) {
			throw new RuntimeException("无DB连接，需先调用openConnInThread");
		}
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sql);
		} catch (SQLException e1) {
			logger.error("", e1);
			throw new RuntimeException("创建PreparedStatement错误");
		}

		ResultSet rs = null;
		try {
			// 设置参数

			int i = 0;
			for (Iterator<String> it = ds.fields.iterator(); it.hasNext();) {
				String field = it.next();
				if (param != null) {
					ps.setObject(i + 1, param.get(field));
				} else {
					ps.setObject(i + 1, null);
				}
				i++;
			}

			rs = ps.executeQuery();
		} catch (SQLException e) {
			logger.error("", e);
		}
		try {
			rv = convertResultSetToList(rs);
		} catch (SQLException e) {
			logger.error("", e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
		}

		return rv;

	}

	public List<Map<String, Object>> findList(String sqlId,
			Map<String, Object> param) {
		List<Map<String, Object>> rv = null;
		ResultSet rs = query(sqlId, param);
		try {
			rv = convertResultSetToList(rs);
		} catch (SQLException e) {
			logger.error("", e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
		}

		return rv;

	}

	public void callSp(String spName, Object[] objItem) {
		Connection conn = tlConn.get();
		CallableStatement callableStatement = null;
		try {
			callableStatement = conn.prepareCall(spName);
			
			for (int j = 0; j < objItem.length; j++) {
				if (objItem[j] instanceof Date) {
					callableStatement.setObject(j + 1, new Timestamp(
							((Date) objItem[j]).getTime()));
				} else {
					callableStatement.setObject(j + 1, objItem[j]);
				}
			}
			callableStatement.execute();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (callableStatement != null) {
				try {
					callableStatement.close();
				} catch (SQLException e) {
					logger.error("", e);
				}
			}
		}
	}
}